#!/bin/bash

# ==============================================================================
# MySQL 自动化性能优化协调器 (v3.0 - 最终报告 & 统一输出)
# ==============================================================================

# --- 检查依赖 (省略) ---

# --- 默认测试场景 ---
ALL_SCENARIOS="read_write,point_select,read_only,write_only,update_index,update_non_index"
SCENARIOS_TO_TEST=""

# --- 解析命令行参数 ---
usage() {
    echo "用法: $0 [-S <场景列表>]"
    echo "参数:"
    echo "  -S <列表>      (可选) 指定要测试的场景,用逗号分隔。默认为全部: ${ALL_SCENARIOS}"
    echo ""
    exit 1
}

while getopts "S:h" opt; do
    case ${opt} in
        S) SCENARIOS_TO_TEST="$OPTARG" ;;
        h) usage ;;
        *) usage ;;
    esac
done

if [ -z "$SCENARIOS_TO_TEST" ]; then
    SCENARIOS_TO_TEST="$ALL_SCENARIOS"
fi
IFS=',' read -r -a SCENARIOS_ARRAY <<< "$SCENARIOS_TO_TEST"


# --- [FEATURE 2] 结果文件和追踪变量 (统一输出目录) ---
TIMESTAMP=$(date +"%Y%m%d_%H%M%S")
TOP_LEVEL_DIR="optimization_run_${TIMESTAMP}"
mkdir -p "$TOP_LEVEL_DIR"

RESULTS_LOG="${TOP_LEVEL_DIR}/optimization_summary.log"
CONFIG_ARCHIVE_DIR="${TOP_LEVEL_DIR}/config_archive"
mkdir -p "$CONFIG_ARCHIVE_DIR"


rm -f "$RESULTS_LOG"
echo "策略名称 | 实例数 | NUMA绑定 | 原始配置 | 归档配置路径 | Sysbench场景 | 数据集(TS*TC) | 并发线程 | 单实例平均TPS | 总TPS" > "$RESULTS_LOG"

# --- [FEATURE 1] 用于最终报告的全局追踪变量 ---
declare -A FINAL_BEST_TPS
declare -A FINAL_BEST_STRATEGY
# ==============================================================================
#                       核心配置区
# ==============================================================================

# #############################################################################
#  FIX: Strategies are now defined by a config file path.
#  格式: "策略名称;实例数;NUMA绑定;设备路径列表;配置文件路径"
# #############################################################################
declare -a STRATEGIES=(
    # --- 单实例策略 ---
    #"1i-perf;1;0;/dev/nvme0n1;mysql_configs/strategy_performance.cnf"

    # --- 4实例策略 (标准配置, 每个实例独占一个NUMA节点和一块NVMe) ---
    #"4i-safe;4;0,1,2,3;/dev/nvme0n1,/dev/nvme1n1,/dev/nvme2n1,/dev/nvme3n1;mysql_configs/strategy_safe.cnf"
    #"4i-perf;4;0,1,2,3;/dev/nvme0n1,/dev/nvme1n1,/dev/nvme2n1,/dev/nvme3n1;mysql_configs/strategy_performance.cnf"
    "4i-extreme;4;0,1,2,3;/dev/nvme0n1,/dev/nvme1n1,/dev/nvme2n1,/dev/nvme3n1;mysql_configs/strategy_extreme.cnf"
    
    # --- 8实例策略 (探索性配置, 2个实例共享一个NUMA节点和一块NVMe) ---
    # 注意：设备路径重复，这意味着实例将共享I/O设备
    #"8i-perf;8;0,1,2,3,0,1,2,3;/dev/nvme0n1,/dev/nvme1n1,/dev/nvme2n1,/dev/nvme3n1,/dev/nvme0n1,/dev/nvme1n1,/dev/nvme2n1,/dev/nvme3n1;mysql_configs/strategy_performance.cnf"
)


# #############################################################################
#  2. 定义Sysbench负载参数矩阵
#  格式: "TABLE_SIZE;TABLE_COUNT;THREADS_RUN"
# #############################################################################
declare -a BENCHMARK_PARAMS=(
    # 场景1: CPU密集型, 低并发 (约30GB数据, 128线程)
    "1000000;32;128"
    "1000000;16;128"
    "1000000;48;128"
    "2000000;32;128"
    #"5000000;32;128"
    "1000000;32;256"
    "2000000;32;256"
    #"5000000;32;256"
    #"1000000;32;512"
    #"2000000;32;512"
    #"5000000;32;512"
    # 场景2: CPU密集型, 高并发 (约30GB数据, 512线程)
    #"5000000;32;512"
)

# ==============================================================================

# --- 主流程 ---
START_PORT=3307
BASE_CONFIG_FILE="mysql_configs/base.cnf"

echo "即将开始多维度测试。测试场景: ${SCENARIOS_TO_TEST}"
echo "!!! 所有结果和配置将保存在统一目录: ${TOP_LEVEL_DIR} !!!"
# read -p "按回车键继续..." # 注释掉以实现全自动运行

for bench_params in "${BENCHMARK_PARAMS[@]}"; do
    IFS=';' read -r table_size table_count threads_run <<< "$bench_params"
    
    echo ""
    echo "######################################################################"
    echo "--- 开始新一轮负载测试 ---"
    echo "--- 数据集: ${table_size}x${table_count}, 并发: ${threads_run} 线程 ---"
    echo "######################################################################"

    declare -a NEEDS_PREPARE_OPTS=("--prepare")

    for strategy in "${STRATEGIES[@]}"; do
        IFS=';' read -r name num_instances numa_nodes device_paths config_file_path <<< "$strategy"

        sub_archive_name="${name}_ts${table_size}_tbl${table_count}_th${threads_run}"
        current_config_archive_path="${CONFIG_ARCHIVE_DIR}/${sub_archive_name}"
        mkdir -p "$current_config_archive_path"

        echo ""
        echo "======================================================================"
        echo "--> 测试策略 [${name}] | 配置文件 [${config_file_path}]"
        echo "--> 配置归档至 [${current_config_archive_path}]"
        echo "======================================================================"
        
        echo "--> 步骤 1: 部署 MySQL 环境 (将重新格式化磁盘)..."
        sudo ./setup_mysql_instances.sh -n "$num_instances" -c "$numa_nodes" -D "$device_paths" -f "$config_file_path" || continue
        
        echo "--> 步骤 1.5: 归档 MySQL 配置文件..."
        for i in $(seq 1 "$num_instances"); do
            cp "/etc/my.cnf.d/mysql${i}.cnf" "${current_config_archive_path}/"
        done
        cp "$config_file_path" "${current_config_archive_path}/"
        if [ -f "$BASE_CONFIG_FILE" ]; then
            cp "$BASE_CONFIG_FILE" "${current_config_archive_path}/"
        fi
        echo "    归档完成。"

        echo "--> 步骤 2: 运行 Sysbench 基准测试 (包含prepare)..."
        ports_to_test=$(seq -s, $START_PORT $((START_PORT + num_instances - 1)))
        
        ./run_sysbench_benchmark.sh -p "$ports_to_test" -S "$SCENARIOS_TO_TEST" \
            --table-size="$table_size" \
            --tables="$table_count" \
            --threads="$threads_run" \
            "${NEEDS_PREPARE_OPTS[@]}" || continue

        echo "--> 步骤 3: 分析结果并更新最终报告..."
        for scenario in "${SCENARIOS_ARRAY[@]}"; do
            total_tps=0
            for i in $(seq 1 "$num_instances"); do
                port=$((START_PORT + i - 1))
                log_file="./sysbench_results/port_${port}/${scenario}_ts${table_size}_tbl${table_count}_th${threads_run}_result.log"
                
                if [ -f "$log_file" ] && grep -q "transactions:" "$log_file"; then
                    tps=$(grep "transactions:" "$log_file" | head -n1 | awk -F'[()]' '{print $2}' | awk '{print $1}')
                    if [[ "$tps" =~ ^[0-9.]+$ ]]; then
                        total_tps=$(echo "$total_tps + $tps" | bc)
                    fi
                fi
            done
            
            if (( $(echo "$total_tps > 0" | bc -l) )); then
                avg_tps=$(echo "scale=2; $total_tps / $num_instances" | bc)
                dataset_tag="${table_size}x${table_count}"
                echo "策略 [${name}] | 场景 [${scenario}] | 数据集 [${dataset_tag}] | 并发 [${threads_run}] -> 总TPS: ${total_tps}"
                echo "${name} | ${num_instances} | ${numa_nodes} | ${config_file_path} | ${current_config_archive_path} | ${scenario} | ${dataset_tag} | ${threads_run} | ${avg_tps} | ${total_tps}" >> "$RESULTS_LOG"

                # --- [FEATURE 1] 更新最终最优结果 ---
                test_case_key="${scenario}_${dataset_tag}_${threads_run}"
                
                # 初始化（如果键不存在）
                if [ -z "${FINAL_BEST_TPS[$test_case_key]}" ]; then
                    FINAL_BEST_TPS[$test_case_key]=0
                fi

                # 比较并更新
                if (( $(echo "$total_tps > ${FINAL_BEST_TPS[$test_case_key]}" | bc -l) )); then
                    FINAL_BEST_TPS[$test_case_key]=$total_tps
                    FINAL_BEST_STRATEGY[$test_case_key]=$name
                    echo "!!! [全局新纪录] 测试用例 [${test_case_key}] -> 策略: ${name}, 总TPS: ${total_tps} !!!"
                fi
            else
                echo "策略 [${name}] 在场景 [${scenario}] 下测试失败或未产生结果。"
            fi
        done
    done
done

# --- [FEATURE 1] 最终综合报告 ---
echo ""
echo "=========================================================================================="
echo "---                           最终综合优化报告                           ---"
echo "---                 所有测试用例的最佳性能配置如下                 ---"
echo "=========================================================================================="
printf "%-25s | %-15s | %-10s | %-20s | %s\n" "Sysbench 场景" "数据集" "并发线程" "最佳策略" "最高总 TPS"
echo "------------------------------------------------------------------------------------------"

# 对所有测试用例的key进行排序，以获得整洁的报告
sorted_keys=$(for key in "${!FINAL_BEST_TPS[@]}"; do echo "$key"; done | sort)

for key in $sorted_keys; do
    # 解析key以获得格式化的输出
    IFS='_' read -r scenario dataset threads <<<"$key"
    
    best_strategy="${FINAL_BEST_STRATEGY[$key]}"
    best_tps="${FINAL_BEST_TPS[$key]}"
    
    printf "%-25s | %-15s | %-10s | %-20s | %s\n" \
        "$scenario" \
        "$dataset" \
        "$threads" \
        "$best_strategy" \
        "$best_tps"
done
echo "=========================================================================================="
echo ""
echo "所有维度的测试均已完成。"
echo "详细日志和配置文件快照已保存在统一目录: ${TOP_LEVEL_DIR}"
echo "=========================================================================================="